﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using MM.DBUtility;


namespace MobileManager
{
    public partial class SsmiAllBySmi : System.Web.UI.Page
    {
        private MM.Model.T_Users userModel;
        private bool bolFlag = true;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["CurUser"] == null)
            {
                Response.Redirect("Login.aspx");
                return;
            }
            else
            {
                userModel = (MM.Model.T_Users)Session["CurUser"];
            }
            if (!IsPostBack)
            {
                BindDdlCustomer(ref ddlCustomer);
                ddlCustomer.Items.Insert(0, new ListItem("全部", "-1"));

                BindDdlODEP(ref ddlODEP);
                ddlODEP.Items.Insert(0, new ListItem("全部", "-1"));

                inputDateFrom.Value = DateTime.Now.ToString("yyyy-MM-dd");
                inputDateTo.Value = DateTime.Now.ToString("yyyy-MM-dd");

                GVDataBind();
            }
        }
        private void BindDdlCustomer(ref DropDownList ddl)
        {
            ddl.DataSource = DbHelperSql.Query("SELECT CNO,CNO+'-'+ CName as Name FROM T_Customers WITH(NOLOCK) ").Tables[0];
            ddl.DataValueField = "CNO";
            ddl.DataTextField = "Name";
            ddl.DataBind();
        }
        private void BindDdlODEP(ref DropDownList ddl)
        {
            ddl.DataSource = DbHelperSql.Query("SELECT ONO,ONO+'-'+ OName as Name FROM T_ODEP WITH(NOLOCK) ").Tables[0];
            ddl.DataValueField = "ONO";
            ddl.DataTextField = "Name";
            ddl.DataBind();
        }

        #region GV Bind
        private void GVDataBind()
        {
            bolFlag = true;

            string strSQL = string.Empty;

            string strF = "SELECT DISTINCT a.CNO,a.AreaNO,a.ONO,a.IMSI,b.CityName,c.ProvinceName,d.OName FROM T_SIMall a, T_Area b,T_Province c,T_ODEP d WITH(NOLOCK) WHERE a.AreaNO=b.AreaNO and a.ONO=b.ONO and b.ProvinceID=c.ProvinceID and a.ONO=d.ONO ";

            string strWhere = string.Empty;
            string strProvinceID = MM.DBUtility.BLSqlData.GetProvinceIDbyQuickName(txtProvince.TextValue);
            if (!string.IsNullOrEmpty(strProvinceID))
                strWhere += string.Format(" AND c.ProvinceID={0}", strProvinceID);
            //if (!string.IsNullOrEmpty(txtIMSI.Text.Trim()))
            //    strWhere += string.Format(" AND a.IMSI LIKE '%{0}%'", txtIMSI.Text.Trim());
            if (!"-1".Equals(ddlCustomer.SelectedValue))
                strWhere += string.Format(" AND a.CNO='{0}'", ddlCustomer.SelectedValue);
            if (!"-1".Equals(ddlODEP.SelectedValue))
                strWhere += string.Format(" AND a.ONO='{0}'", ddlODEP.SelectedValue);
            if (!string.IsNullOrEmpty(inputDateFrom.Value) && !string.IsNullOrEmpty(inputDateTo.Value))
                strWhere += string.Format(" AND a.CreateTime BETWEEN '{0}' AND '{1}' ", inputDateFrom.Value, Convert.ToDateTime(inputDateTo.Value).AddDays(1).ToString("yyyy-MM-dd"));

            strF += strWhere;

            strSQL = "SELECT * FROM (SELECT f.CNO,f.CityName,f.ProvinceName,f.OName,f.CNO+';'+f.AreaNO+';'+f.ONO as AllArg,count(f.IMSI) as SMInum FROM (" + strF + ") AS f ";

            strSQL += " GROUP BY f.CNO,f.AreaNO,f.CityName,f.ProvinceName,f.ONO,f.OName) AS A ";

            AspNetPager1.RecordCount = DbHelperSql.GetCount("SELECT COUNT(1) FROM (" + strSQL + ") AS B");

            string strOrder = string.Format(" ORDER BY A.{0} {1}", ddlOrder.SelectedValue, ddlAD.SelectedValue);

            if (AspNetPager1.CurrentPageIndex == 1)
                strSQL = string.Format(strSQL.Replace("SELECT *", "SELECT TOP {0} * "), AspNetPager1.PageSize) + strOrder;
            else
                strSQL = string.Format("SELECT TOP {0} * FROM ({1}) AS F WHERE rn BETWEEN {2} AND {3}", AspNetPager1.PageSize, strSQL.Replace("SELECT *", string.Format("SELECT *,row_number() over({0}) rn ", strOrder)), (AspNetPager1.CurrentPageIndex - 1) * AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex * AspNetPager1.PageSize - 1);


            DataSet ds = DbHelperSql.Query(strSQL);
            GVinfo.DataSource = ds;
            GVinfo.DataBind();
            //ViewState["DataTemp"] = ds;
            lbCount.Text = "记录数：" + AspNetPager1.RecordCount;


            object o = DbHelperSql.GetSingle("SELECT COUNT(*) FROM (" + strF + ") AS X");
            if (o != null)
            {
                lbRnumAll.Visible = true;
                lbRnumAll.Text = "总SMI数：" + o.ToString();
            }
            else lbRnumAll.Visible = false;

        }
        private void GVDataBindAll()
        {
            bolFlag = false;

            string strSQL = string.Empty;

            string strF = "SELECT DISTINCT a.CNO,a.AreaNO,a.ONO,a.IMSI FROM T_SIMall a WITH(NOLOCK) ";

            strSQL = "SELECT * FROM (SELECT f.CNO,b.CityName,c.ProvinceName,d.OName,f.CNO+';'+b.AreaNO+';'+d.ONO as AllArg,count(f.IMSI) as SMInum FROM (" + strF + ") AS f,T_Area b,T_Province c,T_ODEP d WITH(NOLOCK) WHERE f.AreaNO=b.AreaNO and f.ONO=b.ONO and b.ProvinceID=c.ProvinceID and f.ONO=d.ONO ";

            strSQL += " GROUP BY f.CNO,b.AreaNO,b.CityName,c.ProvinceName,d.ONO,d.OName) AS A";

            AspNetPager1.RecordCount = DbHelperSql.GetCount("SELECT COUNT(1) FROM (" + strSQL + ") AS B");

            string strOrder = string.Format(" ORDER BY A.{0} {1}", ddlOrder.SelectedValue, ddlAD.SelectedValue);

            if (AspNetPager1.CurrentPageIndex == 1)
                strSQL = string.Format(strSQL.Replace("SELECT *", "SELECT TOP {0} * "), AspNetPager1.PageSize) + strOrder;
            else
                strSQL = string.Format("SELECT TOP {0} * FROM ({1}) AS F WHERE rn BETWEEN {2} AND {3}", AspNetPager1.PageSize, strSQL.Replace("SELECT *", string.Format("SELECT *,row_number() over({0}) rn ", strOrder)), (AspNetPager1.CurrentPageIndex - 1) * AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex * AspNetPager1.PageSize - 1);

            DataSet ds = DbHelperSql.Query(strSQL);
            GVinfo.DataSource = ds;
            GVinfo.DataBind();
            //ViewState["DataTemp"] = ds;
            lbCount.Text = "记录数：" + AspNetPager1.RecordCount;


            object o = DbHelperSql.GetSingle("SELECT COUNT(*) FROM (" + strF + ")  AS X");
            if (o != null)
            {
                lbRnumAll.Visible = true;
                lbRnumAll.Text = "总SMI数：" + o.ToString();
            }
            else lbRnumAll.Visible = false;

        }
        protected void GVinfo_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            //GVinfo.PageIndex = e.NewPageIndex;
            //GVinfo.DataSource = (DataSet)ViewState["DataTemp"];
            //GVinfo.DataBind();
        }
        protected void AspNetPager1_PageChanged(object sender, EventArgs e)
        {
            if (bolFlag)
                GVDataBind();
            else
                GVDataBindAll();
        }
        #endregion

        protected void btnQuery_Click(object sender, EventArgs e)
        {
            try
            {
                if (!string.IsNullOrEmpty(inputDateFrom.Value) && !string.IsNullOrEmpty(inputDateTo.Value))
                {
                    if (Convert.ToDateTime(inputDateFrom.Value) > Convert.ToDateTime(inputDateTo.Value))
                    {
                        MM.Common.Common.PageStartAlert(this.Page, "请求时间From必须小于等于To！");
                        return;
                    }
                }
                GVDataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected void btnQueryAll_Click(object sender, EventArgs e)
        {
            try
            {
                inputDateFrom.Value = "";
                inputDateTo.Value = "";
                GVDataBindAll();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        protected void lbtnRnum_Click(object sender, EventArgs e)
        {
            try
            {
                LinkButton lbtn = (LinkButton)sender;
                string strArg = lbtn.CommandArgument;
                if (!string.IsNullOrEmpty(strArg))
                {
                    string[] arr = strArg.Split(';');

                    string strSQL = "SELECT * FROM (SELECT a.CNO,a.IMSI,b.CityName,c.ProvinceName,d.OName,COUNT(a.IMSI) AS SMInum,a.CNO+';'+b.AreaNO+';'+d.ONO+';'+a.IMSI as AllArg  FROM T_SIMall a,T_Area b,T_Province c,T_ODEP d WITH(NOLOCK) WHERE a.AreaNO=b.AreaNO and a.ONO=b.ONO and b.ProvinceID=c.ProvinceID and a.ONO=d.ONO ";
                    strSQL += string.Format(" AND a.CNO='{0}' AND b.AreaNO='{1}' AND d.ONO='{2}'", arr[0], arr[1], arr[2]);
                    if (!string.IsNullOrEmpty(inputDateFrom.Value) && !string.IsNullOrEmpty(inputDateTo.Value))
                        strSQL += string.Format(" AND a.CreateTime BETWEEN '{0}' AND '{1}' ", inputDateFrom.Value, Convert.ToDateTime(inputDateTo.Value).AddDays(1).ToString("yyyy-MM-dd"));
                    strSQL += " GROUP BY a.CNO,b.AreaNO,d.ONO,a.IMSI,b.CityName,c.ProvinceName,d.OName) AS F ORDER BY SMInum DESC";

                    DataSet ds = DbHelperSql.Query(strSQL);
                    gvDetail0.DataSource = ds;
                    gvDetail0.DataBind();
                    ViewState["DataTempDetail"] = ds;
                    lbCountDetail.Text = "记录数：" + ds.Tables[0].Rows.Count;

                    divMain.Visible = false;
                    divDetail.Visible = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected void gvDetail0_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            try
            {
                gvDetail0.PageIndex = e.NewPageIndex;
                gvDetail0.DataSource = (DataSet)ViewState["DataTempDetail"];
                gvDetail0.DataBind();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected void btnBack_Click(object sender, EventArgs e)
        {
            try
            {
                divMain.Visible = true;
                divDetail.Visible = false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        protected void lbtnRnum0_Click(object sender, EventArgs e)
        {
            try
            {
                LinkButton lbtn = (LinkButton)sender;
                string strArg = lbtn.CommandArgument;
                if (!string.IsNullOrEmpty(strArg))
                {
                    string[] arr = strArg.Split(';');

                    string strSQL = "SELECT a.CNO,a.IMSI,a.CreateTime,b.CityName,c.ProvinceName,d.OName FROM T_SIMall a,T_Area b,T_Province c,T_ODEP d WITH(NOLOCK) WHERE a.AreaNO=b.AreaNO and a.ONO=b.ONO and b.ProvinceID=c.ProvinceID and a.ONO=d.ONO ";
                    strSQL += string.Format(" AND a.CNO='{0}' AND b.AreaNO='{1}' AND d.ONO='{2}' AND a.IMSI='{3}'", arr[0], arr[1], arr[2], arr[3]);
                    if (!string.IsNullOrEmpty(inputDateFrom.Value) && !string.IsNullOrEmpty(inputDateTo.Value))
                        strSQL += string.Format(" AND a.CreateTime BETWEEN '{0}' AND '{1}' ", inputDateFrom.Value, Convert.ToDateTime(inputDateTo.Value).AddDays(1).ToString("yyyy-MM-dd"));
                    strSQL += " ORDER BY a.CreateTime DESC";

                    DataSet ds = DbHelperSql.Query(strSQL);
                    gvDetail.DataSource = ds;
                    gvDetail.DataBind();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
